Create Housing Entities/Relationships
-- Create Housing Groups ***********************************************************
DECLARE	@return_value int

EXEC	@return_value = [dbo].[WriteDataToDB]
		@RequestorEntityID = 0,
		@DataTableType = N'Entity',
		@JSONData = N'{ "EntityID": "-1", "GroupName": "0", "EntityType": "HousingDIV", "EntitySubType": "HousingGroup", "ShortRelationshipName": "SC_HOUSING", "Entity1ID": "10000" }',
		@DataID = -1

SELECT	'Return Value' = @return_value

GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[WriteDataToDB]
		@RequestorEntityID = 0,
		@DataTableType = N'Entity',
		@JSONData = N'{ "EntityID": "-1", "GroupName": "1", "EntityType": "HousingDIV", "EntitySubType": "HousingGroup", "ShortRelationshipName": "SC_HOUSING", "Entity1ID": "10000" }',
		@DataID = -1

SELECT	'Return Value' = @return_value

GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[WriteDataToDB]
		@RequestorEntityID = 0,
		@DataTableType = N'Entity',
		@JSONData = N'{ "EntityID": "-1", "GroupName": "2", "EntityType": "HousingDIV", "EntitySubType": "HousingGroup", "ShortRelationshipName": "SC_HOUSING", "Entity1ID": "10000" }',
		@DataID = -1

SELECT	'Return Value' = @return_value

GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[WriteDataToDB]
		@RequestorEntityID = 0,
		@DataTableType = N'Entity',
		@JSONData = N'{ "EntityID": "-1", "GroupName": "3", "EntityType": "HousingDIV", "EntitySubType": "HousingGroup", "ShortRelationshipName": "SC_HOUSING", "Entity1ID": "10000" }',
		@DataID = -1

SELECT	'Return Value' = @return_value

GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[WriteDataToDB]
		@RequestorEntityID = 0,
		@DataTableType = N'Entity',
		@JSONData = N'{ "EntityID": "-1", "GroupName": "4", "EntityType": "HousingDIV", "EntitySubType": "HousingGroup", "ShortRelationshipName": "SC_HOUSING", "Entity1ID": "10000" }',
		@DataID = -1

SELECT	'Return Value' = @return_value

GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[WriteDataToDB]
		@RequestorEntityID = 0,
		@DataTableType = N'Entity',
		@JSONData = N'{ "EntityID": "-1", "GroupName": "5", "EntityType": "HousingDIV", "EntitySubType": "HousingGroup", "ShortRelationshipName": "SC_HOUSING", "Entity1ID": "10000" }',
		@DataID = -1

SELECT	'Return Value' = @return_value

GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[WriteDataToDB]
		@RequestorEntityID = 0,
		@DataTableType = N'Entity',
		@JSONData = N'{ "EntityID": "-1", "GroupName": "6", "EntityType": "HousingDIV", "EntitySubType": "HousingGroup", "ShortRelationshipName": "SC_HOUSING", "Entity1ID": "10000" }',
		@DataID = -1

SELECT	'Return Value' = @return_value

GO

-- Link Housing to GradeLevel ******************************************************
SELECT 'G_HasHousing' AS ShortRelationshipName,'24607' AS Entity1ID,'24600' AS Entity2ID UNION 
SELECT 'G_HasHousing' AS ShortRelationshipName,'24608' AS Entity1ID,'24601' AS Entity2ID UNION 
SELECT 'G_HasHousing' AS ShortRelationshipName,'24609' AS Entity1ID,'24601' AS Entity2ID UNION 
SELECT 'G_HasHousing' AS ShortRelationshipName,'24610' AS Entity1ID,'24601' AS Entity2ID UNION 
SELECT 'G_HasHousing' AS ShortRelationshipName,'24611' AS Entity1ID,'24601' AS Entity2ID UNION 
SELECT 'G_HasHousing' AS ShortRelationshipName,'24612' AS Entity1ID,'24602' AS Entity2ID UNION 
SELECT 'G_HasHousing' AS ShortRelationshipName,'24613' AS Entity1ID,'24602' AS Entity2ID UNION 
SELECT 'G_HasHousing' AS ShortRelationshipName,'24614' AS Entity1ID,'24603' AS Entity2ID UNION 
SELECT 'G_HasHousing' AS ShortRelationshipName,'24615' AS Entity1ID,'24604' AS Entity2ID UNION 
SELECT 'G_HasHousing' AS ShortRelationshipName,'24616' AS Entity1ID,'24605' AS Entity2ID UNION 
SELECT 'G_HasHousing' AS ShortRelationshipName,'24617' AS Entity1ID,'24605' AS Entity2ID UNION 
SELECT 'G_HasHousing' AS ShortRelationshipName,'24618' AS Entity1ID,'24606' AS Entity2ID UNION 
SELECT 'G_HasHousing' AS ShortRelationshipName,'24619' AS Entity1ID,'24606' AS Entity2ID

-- CREATE Dorms ********************************************************************
select 'SELECT -1 AS EntityID,''HousingDIV'' AS EntityType,''Dorm'' AS EntitySubType, ''' + Housing + ''' AS DormName,''' + CAST([Boarding?] AS NVARCHAR) + ''' AS Boarding,''' + COALESCE(CAST(Capacity AS NVARCHAR),'0') + ''' AS Capacity,
	''' + CAST([Titchie?] AS NVARCHAR) + ''' AS Titchie,''' + CASE WHEN Sex = 1 THEN 'Male' ELSE 'Female' END + ''' AS Gender, ' +
	'''H_HOUSING'' AS ShortRelationshipName,''' + CAST(HG.EntityID AS NVARCHAR) + ''' AS Entity1ID' +
	' UNION'
--SELECT *
FROM OpenQUERY(STUDData97,'SELECT * FROM [Housing]') H
INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
								 @NOSQL = ''SELECT * FROM {HousingDIV.HousingGroup}''') AS HG
	ON HG.GroupName = H.HousingGroup



